package com.sherry.dao.impl;

import com.sherry.dao.IOrderDao;
import com.sherry.model.Order;
import com.sherry.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements IOrderDao {
    private Connection connection =null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    @Override
    public List<Order> getOrderList(String orderNo, int offSize, int pageSize) {
        connection= DBManager.getConnByC3p0();

        String sql="";
        List<Order> orderList=new ArrayList<>();  //多条数据
        Order order =null;  //单条数据
//        没有orderNo说明是查询所有水果数据，有orderNo说明是输入的分页查询

        try {
            //orderNo没有值
            if(orderNo==null||"".equals(orderNo)){
                //表名爆红，一般是database里数据库没刷新，新建的表没被识别进来
                //已经实现了分页查询
                sql="select * from `orders` limit ?,? ";
                psmt=connection.prepareStatement(sql);
//            prepareStatement需要放在try里,catch，不然会报红
                psmt.setInt(1,offSize);
                psmt.setInt(2,pageSize);

            }
            else {
//                也可以用concat字符串拼接来模糊查询
//              sql="select * from fruit where fname like concat('%'+?+'%') limit ?,? ";
                sql="select * from `orders` where orderNo like ? limit ?,? ";
                psmt=connection.prepareStatement(sql);
                //fname模糊查询，%表示前后还能有其他文字
                orderNo="%"+orderNo+"%";
                psmt.setString(1,orderNo);
                psmt.setInt(2,offSize);
                psmt.setInt(3,pageSize);

            }
            rs=psmt.executeQuery();
//            返回的只有单条数据，可以用if,多条要用while循环返回的多条
//            处理数据，遍历列表数据（数据库）——》转为Java数据
            while (rs.next()){
                order=new Order();
                order.setOrderId(rs.getInt("orderId"));
                order.setUserId(rs.getInt("userId"));
                order.setOrderNo(rs.getString("orderNo"));
                order.setCreateTime(rs.getTimestamp("createTime"));
                order.setTotalAccount(rs.getDouble("totalAccount"));
                order.setOrderPlace(rs.getString("orderPlace"));
                order.setCompanyName(rs.getString("companyName"));

//                多条数据的组装
                orderList.add(order);
            }
            System.out.println(orderList);
        }catch (SQLException e){
            e.printStackTrace();
        }

        return orderList;
    }

    @Override
    public Integer getOrderCount(String orderNo) {
        String sql=null;
        Integer count=-1;  //记录总条数
        try {
            //fname没有值
            if(orderNo==null||"".equals(orderNo)){
                sql="SELECT COUNT(1) FROM orders";
                psmt = connection.prepareStatement(sql);
            }
            else {
                // 按 fname 模糊查询总数
                sql = "SELECT COUNT(1) FROM orders WHERE orderNo LIKE ?";
                psmt = connection.prepareStatement(sql);
                orderNo = "%" + orderNo + "%";
                psmt.setString(1, orderNo);
            }
            rs=psmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);  // 获取总记录数
                System.out.println("总条数= "+count);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public Integer delOrderByNo(String orderNo) {
        String sql="delete from orders where `orderNo`=?";
        Object[] param=new Object[]{orderNo};
        return DBManager.executeUpdate(sql,param);
    }

    @Override
    public Integer updateOrder(Order order) {
        String sql="update orders set companyName=?,orderPlace=? where orderNo=?";
        Object[] param=new Object[]{order.getCompanyName(),order.getOrderPlace(),order.getOrderNo()};
        return DBManager.executeUpdate(sql,param);
    }
}
